from PyQt4.QtCore import *
from PyQt4.QtGui import *
from PyQt4.QtSql import *
import Ui_meter
import master.utility as utility

class Meter(QDialog, Ui_meter.Ui_meterDlg):
  def __init__(self, dbConn, dataGlob,  parent=None):
        super(Meter, self).__init__(parent)
        self.setupUi(self)       
        self.initMenu()
        self.db = dbConn
        self.dataGlob = dataGlob
        self.util = utility.Utility(self.db)
        self.per_le.setText(self.util.getServerToday().date().toString("MMyy"))
        self.meter_pgb.setVisible(False)
        
        self.mastModel = meterModel(self)
#        self.mastModel = QSqlTableModel(self)
        self.mastModel.setTable("mater_meteran")
        self.mastModel.setEditStrategy(QSqlTableModel.OnManualSubmit)
        self.mastModel.setHeaderData(0, Qt.Horizontal, QVariant("Doc No"))
        self.mastModel.setHeaderData(1, Qt.Horizontal, QVariant("Unit"))
        self.mastModel.setHeaderData(2, Qt.Horizontal, QVariant("Watt"))
        self.mastModel.setHeaderData(3, Qt.Horizontal, QVariant("CT"))
        self.mastModel.setHeaderData(4, Qt.Horizontal, QVariant("Meter ELC"))
        self.mastModel.setHeaderData(5, Qt.Horizontal, QVariant("ELC Start"))
        self.mastModel.setHeaderData(6, Qt.Horizontal, QVariant("ELC End"))
        self.mastModel.setHeaderData(7, Qt.Horizontal, QVariant("ELC Usage"))
        self.mastModel.setHeaderData(8, Qt.Horizontal, QVariant("Usage*CT"))
        self.mastModel.setHeaderData(9, Qt.Horizontal, QVariant("Meter WTR"))
        self.mastModel.setHeaderData(10, Qt.Horizontal, QVariant("WTR Start"))
        self.mastModel.setHeaderData(11, Qt.Horizontal, QVariant("WTR End"))
        self.mastModel.setHeaderData(12, Qt.Horizontal, QVariant("WTR Usage"))
        self.mastModel.setHeaderData(13, Qt.Horizontal, QVariant("Meter GAS"))
        self.mastModel.setHeaderData(14, Qt.Horizontal, QVariant("GAS Start"))
        self.mastModel.setHeaderData(15, Qt.Horizontal, QVariant("GAS End"))
        self.mastModel.setHeaderData(16, Qt.Horizontal, QVariant("GAS Usage"))
        self.mastModel.setHeaderData(17, Qt.Horizontal, QVariant("Unit Name"))
        
        self.master_tv.setModel(self.mastModel)
        self.master_tv.setItemDelegate(MasterDelegate(self))
#        self.master_tv.setSelectionMode(QTableView.SingleSelection)
        self.master_tv.setSelectionBehavior(QTableView.SelectRows)        
        
        
  @pyqtSignature("")
  def on_per_le_editingFinished(self):
      if self.per_le.text() != "":
          self.per_cb.setCheckState(Qt.Checked)
      else:
          self.per_cb.setCheckState(Qt.Unchecked)
        
  def initMenu(self):
      self.retrieveAct = QAction(QIcon(":/retrieve.ico"), self.tr("Retrieve"), self)
      self.connect(self.retrieveAct, SIGNAL("triggered()"), self.retrieveData)
      self.sortAct = QAction(QIcon(":/sort.ico"), self.tr("Sort"), self)
      
      self.saveAct = QAction(QIcon(":/add.ico"), self.tr("Save"), self)
      self.connect(self.saveAct, SIGNAL("triggered()"), self.saveData)
      self.genAct = QAction(QIcon(":/add.ico"), self.tr("Generate"), self)
      self.connect(self.genAct, SIGNAL("triggered()"), self.generateData)
      self.validAct = QAction(QIcon(":/add.ico"), self.tr("Validate"), self)
      self.connect(self.validAct, SIGNAL("triggered()"), self.validateData)
      
      self.addAct = QAction(QIcon(":/add.ico"), self.tr("Add"), self)
      self.connect(self.addAct, SIGNAL("triggered()"), self.addData)
      self.editAct = QAction(QIcon(":/edit.ico"), self.tr("Edit"), self)
      self.connect(self.editAct, SIGNAL("triggered()"), self.editData)
      self.delAct = QAction(QIcon(":/del.ico"), self.tr("Delete"), self)
      self.connect(self.delAct, SIGNAL("triggered()"), self.delData)
      self.printAct = QAction(QIcon("res/print.ico"), self.tr("Print Preview"), self)

  def retrieveData(self):
      #TODO: filter retrieve data, tanpa join
      query = QSqlQuery("select no_meter, unit, format(daya/1000,2), format(ct,0), mtr_list, l_awal, l_akhir, format((l_akhir - l_awal),0), format((l_akhir - l_awal)*ct,0), mtr_air, a_awal, a_akhir, format((a_akhir - a_awal),0), mtr_gas, g_awal, g_akhir, format((g_akhir - g_awal),0), nama_unit, daya, ct, validasi, bill from mater_meteran where no_meter like '"+self.per_le.text()+"%'")      
#      query = QSqlQuery("select m.no_meter, m.unit, format(m.daya/1000,2), format(m.ct,0), m.mtr_list, m.l_awal, m.l_akhir, format((m.l_akhir - m.l_awal),0), format((m.l_akhir - m.l_awal)*m.ct,0), m.mtr_air, m.a_awal, m.a_akhir, format((m.a_akhir - m.a_awal),0), m.mtr_gas, m.g_awal, m.g_akhir, format((m.g_akhir - m.g_awal),0), m.nama_unit, m.daya, m.ct, m.validasi, m.bill from mater_meteran m left outer join base_unit b on b.unit = left(m.unit,8) where m.no_meter like '"+self.per_le.text()+"%' and b.nama_unit like '"+self.name_le.text()+"%'")      
      self.mastModel.setQuery(query)
#      self.mastModel.setFilter("no_meter like '"+self.per_le.text()+"%'")
#      self.mastModel.select()
      self.master_tv.setColumnWidth(0, 70)
      self.master_tv.setColumnWidth(1, 85)
      self.master_tv.setColumnWidth(2, 40)
      self.master_tv.setColumnWidth(3, 30)
      self.master_tv.setColumnWidth(4, 90)
      self.master_tv.setColumnWidth(5, 70)
      self.master_tv.setColumnWidth(6, 70)
      self.master_tv.setColumnWidth(7, 70)
      self.master_tv.setColumnWidth(8, 70)
      self.master_tv.setColumnWidth(9, 90)
      self.master_tv.setColumnWidth(10, 70)
      self.master_tv.setColumnWidth(11, 70)
      self.master_tv.setColumnWidth(12, 80)
      self.master_tv.setColumnWidth(13, 90)
      self.master_tv.setColumnWidth(14, 70)
      self.master_tv.setColumnWidth(15, 70)
      self.master_tv.setColumnWidth(16, 80)
      self.master_tv.setColumnWidth(17, 200)
      self.master_tv.setColumnHidden(18, True)
      self.master_tv.setColumnHidden(19, True)
      self.master_tv.setColumnHidden(20, True)
      self.master_tv.setColumnHidden(21, True)
      self.master_tv.resizeRowsToContents()
      
  def saveData(self):
      index = self.master_tv.currentIndex()
      self.db.transaction()
      self.mastModel.submitAll()
      self.db.commit()
      self.master_tv.selectRow(index.row())
      
  def generateData(self):
      hari = self.util.getServerToday().date()
      per = hari.toString("MMyy")
      hari_bln_lalu = QDate(hari.year(),  hari.month(),  1)
      hari_bln_lalu = hari_bln_lalu.addDays(-1)
      per_lalu = hari_bln_lalu.toString("MMyy")
      
      query = QSqlQuery()
      query.exec_("select count(*) from base_unit_hist where date_format(periode,'%m%y') = '"+per+"'")
      query.next()
      if query.value(0).toInt()[0] < 1:
          QMessageBox.warning(self, "Meter Utility", "Process base unit before generate! Please inform billing staff.")
          return
      
      query.exec_("select count(*) from mater_meteran where left(no_meter,4) = '"+per+"'")
      query.next()
      if query.value(0).toInt()[0] > 0:
          QMessageBox.information(self, "Meter Utility", "Meter utility already generated. Use retrieve instead!")
          return
      else:
          pesan = QMessageBox.question(self, "Meter Utility", "Meter utility on "+per+" will be generated! Continue?", QMessageBox.Yes|QMessageBox.No)
          if pesan == QMessageBox.Yes:
              self.master_tv.setEnabled(False)
              self.meter_pgb.setVisible(True)
              
              unitModel = QSqlQueryModel(self)
              qStr = "select kode_unit, unit, stat_bill_elc, stat_bill_air, stat_bill_gas, nama_unit, util_bill_to, status_join from base_unit "
              filt = " where (stat_bill_elc = 'Y' or stat_bill_air = 'Y' or stat_bill_gas = 'Y') and status_join <> 'J' "
              urut = " order by unit "
              qStr = qStr + filt + urut
              unitModel.setQuery(qStr, self.db)
              jum_brs = unitModel.rowCount()
              self.meter_pgb.setRange(0, jum_brs)              
              self.db.transaction()
              
              for i in range(jum_brs):
                  self.mastModel.insertRow(i)
                  noDok = per + "-" + "0"*(4 - len(str(i+1))) + str(i+1)
                  self.mastModel.setData(self.mastModel.index(i, 0), QVariant(noDok))
                  
                  id_unit = unitModel.record(i).value("kode_unit").toString()
                  kode_unit = unitModel.record(i).value("unit").toString()
                  if unitModel.record(i).value("stat_bill_elc").toString() == "Y":
                      self.mastModel.setData(self.mastModel.index(i, 6), QVariant(0))
                      awal = 0
                      query.exec_("select l_akhir from mater_meteran where left(no_meter,4) = '"+per_lalu+"' and left(unit,8) = '"+kode_unit+"'")
                      while query.next():
                          awal = query.value(0).toInt()[0]
                      self.mastModel.setData(self.mastModel.index(i, 5), QVariant(awal))                      
                      query.exec_("select m.no_meter, t.tarif_daya, t.rasio from base_unit_det_bill m left outer join master_tarif_detail t on t.kode_tarif = m.kode_tarif where m.kode_unit = '"+id_unit+"' and m.tipe_tagih = 'ELC' limit 0,1")
                      while query.next():
                          self.mastModel.setData(self.mastModel.index(i, 4), query.value(0))
                          self.mastModel.setData(self.mastModel.index(i, 18), query.value(1))
                          self.mastModel.setData(self.mastModel.index(i, 19), query.value(2))
              
                  if unitModel.record(i).value("stat_bill_air").toString() == "Y":
                      self.mastModel.setData(self.mastModel.index(i, 11), QVariant(0))
                      awal = 0
                      query.exec_("select a_akhir from mater_meteran where left(no_meter,4) = '"+per_lalu+"' and left(unit,8) = '"+kode_unit+"'")
                      while query.next():
                          awal = query.value(0).toInt()[0]
                      self.mastModel.setData(self.mastModel.index(i, 10), QVariant(awal))
                      query.exec_("select no_meter from base_unit_det_bill where kode_unit = '"+id_unit+"' and tipe_tagih = 'WTR'  limit 0,1")
                      while query.next():
                          self.mastModel.setData(self.mastModel.index(i, 9), query.value(0))
              
                  if unitModel.record(i).value("stat_bill_gas").toString() == "Y":
                      self.mastModel.setData(self.mastModel.index(i, 15), QVariant(0))
                      awal = 0
                      query.exec_("select g_akhir from mater_meteran where left(no_meter,4) = '"+per_lalu+"' and left(unit,8) = '"+kode_unit+"'")
                      while query.next():
                          awal = query.value(0).toInt()[0]
                      self.mastModel.setData(self.mastModel.index(i, 14), QVariant(awal))
                      query.exec_("select no_meter from base_unit_det_bill where kode_unit = '"+id_unit+"' and tipe_tagih = 'GAS'  limit 0,1")
                      while query.next():
                          self.mastModel.setData(self.mastModel.index(i, 13), query.value(0))
              
                  if unitModel.record(i).value("util_bill_to").toString() == "3":
                      kode_unit += "-D"
                  else:
                      if unitModel.record(i).value("status_join").toString() == "N":
                          kode_unit += "-S"
                      else:
                          kode_unit += "-G"                          
                  self.mastModel.setData(self.mastModel.index(i, 1), QVariant(kode_unit))
                  
                  self.mastModel.setData(self.mastModel.index(i, 21), QVariant("N"))
                  nama = unitModel.record(i).value("nama_unit").toString()
                  self.mastModel.setData(self.mastModel.index(i, 17), QVariant(nama))
                  self.meter_pgb.setValue(i)
                  
              self.mastModel.submitAll()
              self.db.commit()
              
              self.meter_pgb.setVisible(False)
              self.master_tv.setEnabled(True)
              
      
  def validateData(self):
      valid = True
      self.meter_pgb.setVisible(True)
      jum_brs = self.mastModel.rowCount()
      self.meter_pgb.setRange(0, jum_brs)
      for i in range(jum_brs):
          unit = self.mastModel.record(i).value("unit").toString()
          if self.mastModel.record(i).value(7).toDouble()[0]  < 0:
              QMessageBox.warning(self, "Meter Utility", "Electricity record unit '"+unit+"' is not valid!")
              self.master_tv.selectRow(i)
              valid = False
              break
          if self.mastModel.record(i).value(12).toDouble()[0]  < 0:
              QMessageBox.warning(self, "Meter Utility", "Water record unit '"+unit+"' is not valid!")
              self.master_tv.selectRow(i)
              valid = False
              break
          if self.mastModel.record(i).value(16).toDouble()[0]  < 0:
              QMessageBox.warning(self, "Meter Utility", "Gas record unit '"+unit+"' is not valid!")
              self.master_tv.selectRow(i)
              valid = False
              break
          self.meter_pgb.setValue(i)
          
      if valid:
          query = QSqlQuery()
          #TODO: per belum didefinisikan
          query.exec_("update mater_meteran set validasi = 'Y' where left(no_meter,4) = '"+per+"'")
      self.meter_pgb.setVisible(False)
      
  def addData(self):
      tes = ""
#      form = businessEdit.BusinessEdit(self.db, "add",  self.dataGlob, self)
#      form.show()

  def editData(self):
      tes = ""

  def delData(self):
      index = self.master_tv.currentIndex()
#      if index.isValid():
#          brs = self.mastModel.record(index.row())
#          id = brs.value("kode_bu").toString()
#          nama = brs.value("nama_bu").toString()
#          if QMessageBox.question(self, "Business Unit","Delete Data : "+nama+"?", QMessageBox.Yes|QMessageBox.No) == QMessageBox.Yes:
#              qMast = QSqlQuery(self.db)
#              self.db.transaction()
#              qMast.exec_("delete from master_bisnis_unit where kode_bu = '"+id+"'")
#              self.db.commit()
#              self.retrieveData()

  def sortData(self):
       tes = ""

  def printData(self):
       tes = ""

  def contextMenuEvent(self, event):
        menu = QMenu(self)
        menu.addAction(self.retrieveAct)
        menu.addAction(self.sortAct)
        menu.addSeparator()
        menu.addAction(self.saveAct)
        menu.addSeparator()
        menu.addAction(self.genAct)
        menu.addAction(self.validAct)
        menu.addSeparator()
        #TODO: atur security, hanyaadmin bisa add, edit, del
        menu.addAction(self.addAct)
        menu.addAction(self.editAct)
        menu.addSeparator()
        menu.addAction(self.delAct)
        menu.addSeparator()
        menu.addAction(self.printAct)
        menu.exec_(event.globalPos())

class meterModel(QSqlTableModel):
    def __init__(self, parent=None):
        super(meterModel, self).__init__(parent)
        
    def flags(self, index):
        flags = QSqlTableModel.flags(self, index)        
        if index.column() < 5:
            flags = Qt.ItemIsSelectable|Qt.ItemIsEnabled
        elif 6 < index.column() < 10:
            flags = Qt.ItemIsSelectable|Qt.ItemIsEnabled
        elif 11 < index.column() < 14:
            flags = Qt.ItemIsSelectable|Qt.ItemIsEnabled
        elif index.column() > 15:
            flags = Qt.ItemIsSelectable|Qt.ItemIsEnabled
        return flags
        
class MasterDelegate(QItemDelegate):
    def __init__(self, parent=None):
        super(MasterDelegate, self).__init__(parent)
        
    def paint(self, painter, option, index):
        myoption = QStyleOptionViewItem(option)
        if 1 < index.column() < 4:
            myoption.displayAlignment |= Qt.AlignRight|Qt.AlignVCenter
        elif 4 < index.column() < 9:
            myoption.displayAlignment |= Qt.AlignRight|Qt.AlignVCenter
        elif 9 < index.column() < 13:
            myoption.displayAlignment |= Qt.AlignRight|Qt.AlignVCenter
        elif 13 < index.column() < 17:
            myoption.displayAlignment |= Qt.AlignRight|Qt.AlignVCenter
        QItemDelegate.paint(self, painter, myoption, index)        
        
        
if __name__ == "__main__":
    import sys
    app = QApplication(sys.argv)
    form = Meter()
    form.show()
    app.exec_()
